Imports System.Data.OleDb
Imports SLibrary.BasicSystem


Namespace Office
    Public Class ExcelReader



        ''' <summary>
        ''' Open Excel as Ole DB Connection (Excel 97)
        ''' </summary>
        ''' <param name="filename"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Shared Function OpenExcel97_OleDbConnection(ByVal filename As String) As OleDbConnection

            Dim excel97_ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                filename & ";Extended Properties=Excel 8.0"
            'Dim excel2010_ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & filename & ";Extended Properties=Excel 12.0"
            Dim excel2010_ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
                filename & ";Extended Properties=Excel 8.0"

            Dim oledbConn As OleDbConnection = Nothing

            Try
                '// Create the connection object
                oledbConn = New OleDbConnection(excel97_ConnectionString)
                oledbConn.Open()
                Return oledbConn

            Catch ex As Exception
                If ex.Message.Trim().StartsWith("The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine") Then
                    '// Create the connection object
                    oledbConn = New OleDbConnection(excel2010_ConnectionString)
                    oledbConn.Open()
                    Return oledbConn

                Else
                    Return oledbConn
                End If

            Finally
                Debug.Print("Finally")

            End Try

        End Function

        ''' <summary>
        ''' Get the connection string for Excel
        ''' </summary>
        ''' <param name="fileName"></param>
        ''' <param name="excelVersion"></param>
        ''' <returns>Connection String</returns>
        ''' <remarks></remarks>
        Public Shared Function GetConnectionString(ByVal fileName As String, ByVal iVersion As ExcelVersion)
            Dim connectionString = ""

            If OS.Is64BitOperatingSystem = False Then
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileName
            Else
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & fileName
            End If

            If iVersion = ExcelVersion.Excel97 Then
                connectionString &= ";Extended Properties=Excel 8.0"
            Else
                connectionString &= ";Extended Properties=Excel 12.0"
            End If
#If DEBUGMODE Then
            Debug.Print(String.Format("connection String = {0}", connectionString))
#End If
            Return connectionString

        End Function
    End Class
End Namespace